#------------------------------------------------------------------------------
# use combined individual level data to generate
# Table S1, S5, 
# data for Figure S1, and Figure 1, 
#==============================================================================

load_library = c('bit64','data.table','fst','future.apply','stringr','logger','vroom','rio','stringr')
invisible(lapply(load_library, function(x) library(x, character.only=TRUE, quietly= TRUE)))

bucket = '/N/project/iuni_doctorshopping/'

ind_combined = read_fst(file.path(bucket,'projects','covid_opioid','data','processed_data',
	'weekly_ses_individuals.fst'), as.data.table=TRUE)

# collapse data by these three periods 
# __ Table S1 
table_s1 = ind_combined[, lapply(.SD, sum, na.rm=TRUE), 
	.SDcols = c('n_week','backpain','neckpain','limbpain'), 
	by=c('PATID','period','year')]

table_s1_backpain = table_s1[backpain > 0, .(n_backpain = .N), by=c('year','period')]
table_s1_neckpain = table_s1[neckpain > 0, .(n_neckpain = .N), by=c('year','period')]
table_s1_limbpain = table_s1[limbpain > 0, .(n_limbpain = .N), by=c('year','period')]

table_s1_tab = merge(table_s1_backpain, table_s1_neckpain, by=c('year','period'))
table_s1_tab = merge(table_s1_tab, table_s1_limbpain, by=c('year','period'))

table_s1 = as.data.frame(t(table_s1_tab)[3:5, ])
rownames(table_s1) = c('backpain','neckpain','limbpain')
colnames(table_s1) = c(paste0('2019P', c(1:3)), paste0('2020P', c(1:3)))
table_s1$category = rownames(table_s1)
rio::export(table_s1, file.path(bucket,'projects','covid_opioid','table_s1.xlsx'), overwrite=TRUE)

